package com.zit.util;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.zit.dao.upTermDAO;
import com.zit.dao.impl.UPTermDAOImpl;
import com.zit.entity.Term;

public class Help {

	public void importExcel(String path) throws Exception {
		Workbook wb = null;
		InputStream is = new FileInputStream(path);
		// 判断EXCEL文件是2007+还是2003文件
		if (path.endsWith(".xls")) {
			wb = new HSSFWorkbook(is);
		} else {
			wb = new XSSFWorkbook(is);
		}
		if (null != wb && wb.getNumberOfSheets() > 0) {
			// 获取工作簿中第1张工作表
			Sheet sheet = wb.getSheetAt(0);
			
		
			upTermDAO ad = new UPTermDAOImpl();

			if (null != sheet) {

				int len = sheet.getLastRowNum();

				Row row = null;

				Term t = null;

				for (int i = 0; i <= len; i++) {

					row = sheet.getRow(i);

					if (null != row) {
						
						
						System.out.println(row.getCell(1));
						
						
						//t = new Term(((Long) getValue(row.getCell(0))).intValue(), (Date) getValue(row.getCell(1)),String.valueOf((Long)getValue(row.getCell(2))));
						
						// 调用添加程序向数据库中添加数据
						ad.addTerm(t);
					}
				}
			}
		}

	}

	public String exportExcel(String path, List<Term> t) throws Exception {
		path+=("\\"+new SimpleDateFormat("yyyyMMddHHmmssSSS").format(new Date())+"address.xls");
		Workbook wb = new HSSFWorkbook();
		Sheet sheet = wb.createSheet("通讯录");
		Row row = null;
		for (int i = 0; i < t.size(); i++) {
			row = sheet.createRow(i);
			row.createCell(0).setCellValue(t.get(i).getId());
			row.createCell(1).setCellValue(t.get(i).getName());
		}
		wb.write(new FileOutputStream(path));
		return path;
	}

	public Object getValue(Cell cell) throws Exception {
		Object obj = null;
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			obj = cell.getStringCellValue();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				obj = dateToObject(cell.getDateCellValue());
			} else {
				obj = doubleToObject(cell.getNumericCellValue());
			}
			break;
		}
		return obj;
	}

	private Object doubleToObject(double d) throws Exception {
		DecimalFormat df = new DecimalFormat("#");
		String sd = df.format(d);
		return df.parseObject(sd);
	}

	private Object dateToObject(Date d) throws Exception {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss SSS");
		String sd = sdf.format(d);
		return sdf.parseObject(sd);
	}

}
